<?php
namespace App\Repositories;

use App\Exceptions\BasicException;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;

class DataSummaryRepository
{

    private $goldFee;

    public function __construct() {
        $this->goldFee = floatval(app('repo_config')->queryConfig('GOLD_FEE')->value);

    }

    private function format($num) {
        return sprintf("%.2f", $num / $this->goldFee);
    }

    public function getUserRegisterNumber(Carbon $date) {
        $startTime = $date->startOfDay()->timestamp;
        $endTime = $date->endOfDay()->timestamp;

        return app("model_user")->where([
            ["register_time", ">=", $startTime],
            ["register_time", "<=", $endTime]
        ])->count();
    }

    public function getAllUserRegisterNumber() {
        return app("model_user")->count();
    }

    public function getUserRechargeMoney(Carbon $date) {
        $startTime = $date->startOfDay()->timestamp;
        $endTime = $date->endOfDay()->timestamp;

        return app("model_user_recharge")->where([
            ["status", "=", 2],
            ["type", "<>", 3],
            ["pay_time", ">=", $startTime],
            ["pay_time", "<=", $endTime],
        ])->sum("money");
    }

    public function getAllUserRechargeMoney() {
        return app("model_user_recharge")->where([
            ["status", "=", 2],
            ["type", "<>", 3]
        ])->sum("money");
    }

    public function getAllAdminRechargeMoney() {
        $gold = app("model_account_log")->where([
            ["change_type", "=", 21]
        ])->sum("user_money");

        return $gold / $this->goldFee;
    }

    public function getSendGiftValue(Carbon $date) {
        $startTime = $date->startOfDay()->timestamp;
        $endTime = $date->endOfDay()->timestamp;

        return app("model_give_gift")->where([
            ["add_time", ">=", $startTime],
            ["add_time", "<=", $endTime],
        ])->sum("total_price");
    }

    public function getJustSendGiftValue(Carbon $date) {
        $startTime = $date->startOfDay()->timestamp;
        $endTime = $date->endOfDay()->timestamp;

        return app("model_give_gift")->where([
            ["type", "=", 0],
            ["add_time", ">=", $startTime],
            ["add_time", "<=", $endTime],
        ])->sum("total_price");
    }

    public function getAllSendGiftValue() {
        return app("model_give_gift")->sum("total_price");
    }

    public function getWithdrawMoney(Carbon $date) {
        $startTime = $date->startOfDay()->timestamp;
        $endTime = $date->endOfDay()->timestamp;

        return app("model_user_withdraw")->where([
            ["status", "=", 2],
            ["audit_time", ">=", $startTime],
            ["audit_time", "<=", $endTime],
        ])->sum("money");
    }

    public function getTotalWithdrawMoney() {
        return app("model_user_withdraw")->where([
            ["status", "=", 2],
        ])->sum("money");
    }

    public function getWaitWithdrawMoney() {
        return app("model_user_withdraw")->where([
            ["status", "=", 1],
        ])->sum("money");
    }

    public function getSocietyStockNumber() {
        return app("model_sc_sociaty")->where([
            ["type", "=", 1],
        ])->count();
    }

    public function getSocietyActiveNumber() {
        $result = DB::connection("mysql_sociaty")->select("
            SELECT
                t1.sociaty_id,
                count( t2.sociaty_id ) AS num 
            FROM
                sc_sociaty AS t1
                LEFT JOIN sc_sociaty AS t2 ON t1.sociaty_id = t2.parent_id 
            WHERE
                t1.type = 1 
            GROUP BY
                t1.sociaty_id 
            HAVING
                num > 0
        ");

        return empty($result) ? 0 : count($result);
    }

    public function getHallStockNumber() {
        return app("model_sc_sociaty")->where([
            ["type", "=", 2],
        ])->count();
    }

    public function getHallActiveNumber() {
        return app("model_sc_sociaty")->where([
            ["type", "=", 2],
            ["parent_id", ">", 0]
        ])->count();
    }

    public function getSocietyAnchorNumber() {
        return app("model_sc_sociaty_user")->count();
    }

    public function getSocietyRoomGiftGold() {
        return app("model_sc_income")->sum("amount");
    }

    public function getSocietyMemberGiftGold() {
        return app("model_sc_income")->where([
            ["type", 1],
        ])->sum("amount");
    }

    public function getUserStockProfitGold() {
        return app("model_user")->sum("earnings");
    }

    public function getUserStockBalanceGold() {
        return app("model_user")->sum("money");
    }

    public function getUserStockGiftGold() {
        $result = DB::connection("mysql")->select("
            SELECT
                sum( t1.number * t2.price ) AS num 
            FROM
                ln_gift_backpack AS t1
                LEFT JOIN ln_gift AS t2 ON t1.gift_id = t2.id
        ");

        return empty($result) ? 0 : $result[0]->num;
    }

    public function getMallProfitGold() {
        return app("model_mall_orders")->whereIn("type", [1, 2])->sum("price");
    }

    public function getNobilityProfitGold() {
        return app("model_nobility_buy")->sum("money");
    }

    public function getRadioProtectProfitGold() {
        return app("model_fm_protect_list")->sum("gold");
    }

    public function getDispatchProfitGold() {
        return app("model_lisence_order")->where([
            ["status", "=" , 6]
        ])->sum("price");
    }

    public function getGameCatCostGold() {
        return app("model_gold_price")->where([
            ["game_type", "=" , 1]
        ])->sum("total_cost");
    }

    public function getGameCatCostGoldDaily(Carbon $date) {
        $startTime = $date->startOfDay()->timestamp;
        $endTime = $date->endOfDay()->timestamp;

        return app("model_gold_price")->where([
            ["game_type", "=", 1],
            ["add_time", ">=", $startTime],
            ["add_time", "<=", $endTime],
        ])->sum("total_cost");
    }

    public function getGameTreeCostGold() {
        return app("model_gold_price")->where([
            ["game_type", "=" , 2]
        ])->sum("total_cost");
    }

    public function getGameTreeCostGoldDaily(Carbon $date) {
        $startTime = $date->startOfDay()->timestamp;
        $endTime = $date->endOfDay()->timestamp;

        return app("model_gold_price")->where([
            ["game_type", "=", 2],
            ["add_time", ">=", $startTime],
            ["add_time", "<=", $endTime],
        ])->sum("total_cost");
    }

    public function getGameVirusCostGold() {
        $number = app("model_virus_price")->count();
        return $number* 200;
    }

    public function getGameTotalStockGiftGold() {
        $result = DB::connection("mysql")->select("
            SELECT
                sum( t1.number * t2.price ) AS num 
            FROM
                ln_gift_backpack AS t1
                LEFT JOIN ln_gift AS t2 ON t1.gift_id = t2.id
        ");

        return empty($result) ? 0 : $result[0]->num;
    }

    public function getGameCatGiftGold() {
        return app("model_gold_price")->where([
            ["game_type", "=" , 1]
        ])->sum("number");
    }

    public function getGameCatGiftGoldDaily(Carbon $date) {
        $startTime = $date->startOfDay()->timestamp;
        $endTime = $date->endOfDay()->timestamp;

        return app("model_gold_price")->where([
            ["game_type", "=", 1],
            ["add_time", ">=", $startTime],
            ["add_time", "<=", $endTime],
        ])->sum("number");
    }

    public function getGameTreeGiftGold() {
        return app("model_gold_price")->where([
            ["game_type", "=" , 2]
        ])->sum("number");
    }

    public function getGameTreeGiftGoldDaily(Carbon $date) {
        $startTime = $date->startOfDay()->timestamp;
        $endTime = $date->endOfDay()->timestamp;

        return app("model_gold_price")->where([
            ["game_type", "=", 2],
            ["add_time", ">=", $startTime],
            ["add_time", "<=", $endTime],
        ])->sum("number");
    }

    public function getGameVirusGiftGold() {
        return app("model_virus_price")->sum("number");
    }

    public function getBlindBoxTotalCostGold() {
        return app("model_gift_box_price")->sum("this_time_gold");
    }

    public function getBlindBoxTotalCostGoldDaily(Carbon $date) {
        $startTime = $date->startOfDay()->timestamp;
        $endTime = $date->endOfDay()->timestamp;

        return app("model_gift_box_price")->where([
            ["add_time", ">=", $startTime],
            ["add_time", "<=", $endTime],
        ])->sum("this_time_gold");
    }

    public function getBlindBoxGiftGold() {
        return app("model_gift_box_price")->sum("number");
    }

    public function getBlindBoxGiftGoldDaily(Carbon $date) {
        $startTime = $date->startOfDay()->timestamp;
        $endTime = $date->endOfDay()->timestamp;

        return app("model_gift_box_price")->where([
            ["add_time", ">=", $startTime],
            ["add_time", "<=", $endTime],
        ])->sum("number");
    }

    public function getCandyTotalNumber() {
        return app("model_red_envelope_user_log")->sum("reward_count");
    }

    public function getCandyGoldDaily(Carbon $date) {
        $startTime = $date->startOfDay()->format("Y-m-d H:i:s");
        $endTime = $date->endOfDay()->format("Y-m-d H:i:s");

        return app("model_red_envelope_user_log")->where([
            ["created_at", ">=", $startTime],
            ["created_at", "<=", $endTime],
        ])->sum("reward_count");
    }

    public function getCandyTotalTimes() {
        return app("model_red_envelope_room_log")->where([
            ["status", 1],
        ])->count();
    }

    public function getCandyBackpackNumber() {
        $globalConfig = app("model_red_envelope_global_config")->first();

        $giftId = $globalConfig['gift_id'];

        return app("model_gift_backpack")->where([
            ["gift_id", $giftId],
        ])->sum("number");
    }

    // 30天内的数据
    public function getLast30Data() {
        $startDate = Carbon::create()->subDays(30)->startOfDay()->format("Y-m-d");
        $endDate = Carbon::create()->subDay()->endOfDay()->format("Y-m-d");

        $res = app("model_data_summary_daily")->where([
            ["date", ">=", $startDate],
            ["date", "<=", $endDate],
            ["is_deleted", "=", 0]
        ])->select([
          "*", DB::raw("has_recharge_money - has_withdraw_money as cash_money")
        ])->orderby("date", "asc")
        ->get();

        $result = [
            "register_user_list" => [],
            "recharge_money_list" => [],
            "gift_value_list" => [],
            "send_gift_value_list" => [],
            "withdraw_money_list" => [],
            "cash_list" => [],
            "cat_cost_list" => [],
            "cat_reward_list" => [],
            "candy_list" => [],
            "blind_box_cost_list" => [],
            "blind_box_reward_list" => [],
        ];

        foreach ($res as $item) {
            $temp = [
                "date" => $item->date
            ];
            $temp['value'] = $item->register_user_number;
            $result['register_user_list'][] = $temp;

            $temp['value'] = $item->has_recharge_money;
            $result['recharge_money_list'][] = $temp;

            $temp['value'] = $item->send_gift_value;
            $result['gift_value_list'][] = $temp;

            $temp['value'] = $item->just_send_gift_value;
            $result['send_gift_value_list'][] = $temp;

            $temp['value'] = $item->has_withdraw_money;
            $result['withdraw_money_list'][] = $temp;

            $temp['value'] = $item->cash_money;
            $result['cash_list'][] = $temp;

            $temp['value'] = $item->tree_cost;
            $result['cat_cost_list'][] = $temp;

            $temp['value'] = $item->tree_reward;
            $result['cat_reward_list'][] = $temp;

            $temp['value'] = $item->candy_reward;
            $result['candy_list'][] = $temp;

            $temp['value'] = $item->blind_box_cost;
            $result['blind_box_cost_list'][] = $temp;

            $temp['value'] = $item->blind_box_reward;
            $result['blind_box_reward_list'][] = $temp;
        }
        $today = Carbon::create();

        $todayRegisterUserNumber = $this->getUserRegisterNumber($today);
        $todayRechargeMoney = $this->getUserRechargeMoney($today);
        $todayGiftValue = $this->getSendGiftValue($today);
        $todayJustGiftValue = $this->getJustSendGiftValue($today);
        $todayWithdrawMoney = $this->getWithdrawMoney($today);
        $todayCash = $todayRechargeMoney - $todayWithdrawMoney;
//        $todayCatCost = $this->getGameCatCostGoldDaily($today);
//        $todayCatReward = $this->getGameCatGiftGoldDaily($today);
        $todayTreeCost = $this->getGameTreeCostGoldDaily($today);
        $todayTreeReward = $this->getGameTreeGiftGoldDaily($today);
        $todayCandy = $this->getCandyGoldDaily($today);
        $todayBlindBoxCost = $this->getBlindBoxTotalCostGoldDaily($today);
        $todayBlindBoxReward = $this->getBlindBoxGiftGoldDaily($today);

        $result['register_user_list'][] = [
            "date" => $today->format("Y-m-d"),
            "value" => $todayRegisterUserNumber
        ];

        $result['recharge_money_list'][] = [
            "date" => $today->format("Y-m-d"),
            "value" => $todayRechargeMoney
        ];

        $result['gift_value_list'][] = [
            "date" => $today->format("Y-m-d"),
            "value" => $todayGiftValue
        ];

        $result['withdraw_money_list'][] = [
            "date" => $today->format("Y-m-d"),
            "value" => $todayWithdrawMoney
        ];

        $result['send_gift_value_list'][] = [
            "date" => $today->format("Y-m-d"),
            "value" => $todayJustGiftValue
        ];

        $result['cash_list'][] = [
            "date" => $today->format("Y-m-d"),
            "value" => $todayCash
        ];

        $result['cat_cost_list'][] = [
            "date" => $today->format("Y-m-d"),
            "value" => $todayTreeCost
        ];

        $result['cat_reward_list'][] = [
            "date" => $today->format("Y-m-d"),
            "value" => $todayTreeReward
        ];

        $result['candy_list'][] = [
            "date" => $today->format("Y-m-d"),
            "value" => $todayCandy
        ];

        $result['blind_box_cost_list'][] = [
            "date" => $today->format("Y-m-d"),
            "value" => $todayBlindBoxCost
        ];

        $result['blind_box_reward_list'][] = [
            "date" => $today->format("Y-m-d"),
            "value" => $todayBlindBoxReward
        ];

        return $result;

    }

    public function getSummaryDataFromDb() {
        $registerUserNumber = $this->getAllUserRegisterNumber();
        $giftGold = $this->getAllSendGiftValue();
        $giftMoney = $this->format($giftGold);
        $onlineRechargeMoney = $this->getAllUserRechargeMoney();
        $adminRechargeMoney = $this->getAllAdminRechargeMoney();
        $rechargeMoney = $onlineRechargeMoney + $adminRechargeMoney;
        $withdrawMoney = $this->getTotalWithdrawMoney();
        $cashMoney = $rechargeMoney - $withdrawMoney;
        $societyStockNumber = $this->getSocietyStockNumber();
        $societyActiveNumber = $this->getSocietyActiveNumber();
        $hallStockNumber = $this->getHallStockNumber();
        $hallActiveNumber = $this->getHallActiveNumber();
        $societyAnchorNumber = $this->getSocietyAnchorNumber();
        $societyRoomGiftGold = $this->getSocietyRoomGiftGold();
        $societyRoomGiftMoney = $this->format($societyRoomGiftGold);
        $societyMemberGiftGold = $this->getSocietyMemberGiftGold();
        $societyMemberGiftMoney = $this->format($societyMemberGiftGold);
        $userStockProfitGold = $this->getUserStockProfitGold();
        $userStockBalanceGold = $this->getUserStockBalanceGold();
        $userStockGiftGold = $this->getUserStockGiftGold();
        $userStockGold = $userStockProfitGold + $userStockBalanceGold + $userStockGiftGold;
        $waitWithdrawGold = $this->getWaitWithdrawMoney();
        $mallProfitGold = $this->getMallProfitGold();
        $nobilityProfitGold = $this->getNobilityProfitGold();
        $radioProtectProfitGold = $this->getRadioProtectProfitGold();
        $dispatchProfitGold = $this->getDispatchProfitGold();
        $gameCatCostGold = $this->getGameCatCostGold();
        $gameTreeCostGold = $this->getGameTreeCostGold();
        $gameVirusCostGold = $this->getGameVirusCostGold();
        $gameTotalCostGold = $gameCatCostGold + $gameTreeCostGold + $gameVirusCostGold;
        $gameCatGiftGold = $this->getGameCatGiftGold();
        $gameTreeGiftGold = $this->getGameTreeGiftGold();
        $gameVirusGiftGold = $this->getGameVirusGiftGold();
        $gameTotalGiftGold = $gameCatGiftGold + $gameTreeGiftGold + $gameVirusGiftGold;
        $gameTotalStockGiftGold = $this->getGameTotalStockGiftGold();
        $blindBoxTotalCostGold = $this->getBlindBoxTotalCostGold();
        $blindBoxGiftGold = $this->getBlindBoxGiftGold();
        $candyTotalNumber = $this->getCandyTotalNumber();
        $candyTotalTimes = $this->getCandyTotalTimes();
        $candyBackpackNumber = $this->getCandyBackpackNumber();
        $totalData = [
            "register_user_number" => $registerUserNumber,
            "gift_money" => $giftMoney,
            "gift_gold" => $giftGold,
            "recharge_money" => $rechargeMoney,
            "online_recharge_money" => $onlineRechargeMoney,
            "admin_recharge_money" => $adminRechargeMoney,
            "withdraw_money" => $withdrawMoney,
            "cash_money" => $cashMoney,
            "society_stock_number" => $societyStockNumber,
            "society_active_number" => $societyActiveNumber,
            "hall_stock_number" => $hallStockNumber,
            "hall_active_number" => $hallActiveNumber,
            "society_anchor_number" => $societyAnchorNumber,
            "society_room_gift_money" => $societyRoomGiftMoney,
            "society_room_gift_gold" => $societyRoomGiftGold,
            "society_member_gift_money" => $societyMemberGiftMoney,
            "society_member_gift_gold" => $societyMemberGiftGold,
            "user_stock_gold" => $userStockProfitGold,
            "user_stock_profit_gold" => $userStockBalanceGold,
            "user_stock_balance_gold" => $userStockGiftGold,
            "user_stock_gift_gold" => $userStockGold,
            "wait_withdraw_gold" => $waitWithdrawGold,
            "mall_profit_gold" => $mallProfitGold,
            "nobility_profit_gold" => $nobilityProfitGold,
            "radio_protect_profit_gold" => $radioProtectProfitGold,
            "dispatch_profit_gold" => $dispatchProfitGold,
            "game_total_cost_gold" => $gameCatCostGold,
            "game_cat_cost_gold" => $gameTreeCostGold,
            "game_tree_cost_gold" => $gameVirusCostGold,
            "game_virus_cost_gold" => $gameTotalCostGold,
            "game_total_gift_gold" => $gameCatGiftGold,
            "game_cat_gift_gold" => $gameTreeGiftGold,
            "game_tree_gift_gold" => $gameVirusGiftGold,
            "game_virus_gift_gold" => $gameTotalGiftGold,
            "game_total_stock_gift_gold" => $gameTotalStockGiftGold,
            "blind_box_total_cost_gold" => $blindBoxTotalCostGold,
            "blind_box_gift_gold" => $blindBoxGiftGold,
            "candy_total_number" => $candyTotalNumber,
            "candy_total_times" => $candyTotalTimes,
            "candy_backpack_number" => $candyBackpackNumber,
        ];

        $last30DayData = $this->getLast30Data();
        $result = array_merge([
            "total_data" => $totalData
        ], $last30DayData);

        return $result;
    }

    public function refreshRedis() {
        $redisKey = "data_summary_cache";
        $result = $this->getSummaryDataFromDb();

        Redis::setex($redisKey, 3600 * 2, json_encode($result));
    }

    // 获取概览数据
    public function getSummaryData() {
        $redisKey = "data_summary_cache";
        $result = Redis::get($redisKey);
        if ($result === false) {
            $result = $this->getSummaryDataFromDb();

            Redis::setex($redisKey, 3600 * 2, json_encode($result));
        } else {
            $result = json_decode($result, 1);
        }

        return $result;
    }

    public function insertWithDate($params, $date) {
        DB::connection("mysql")->transaction(function () use($params, $date) {
            $nowTime = time();
            // 将存在的本日数据删除
            app("model_data_summary_daily")
                ->where([
                    ["is_deleted", "=", 0],
                    ["date", "=", $date]
                ])
                ->update([
                    "is_deleted" => 1,
                    "update_time" => $nowTime
                ]);

            $insertParams = [
                "date" => $date,
                "register_user_number" => $params['register_user_number'],
                "has_recharge_money" => $params['has_recharge_money'],
                "send_gift_value" => $params['send_gift_value'],
                "has_withdraw_money" => $params['has_withdraw_money'],
                "just_send_gift_value" => $params['just_send_gift_value'],
                "cat_cost" => $params['cat_cost'],
                "cat_reward" => $params['cat_reward'],
                "tree_cost" => $params['tree_cost'],
                "tree_reward" => $params['tree_reward'],
                "candy_cost" => $params['candy_cost'],
                "candy_reward" => $params['candy_reward'],
                "blind_box_cost" => $params['blind_box_cost'],
                "blind_box_reward" => $params['blind_box_reward'],
                "is_deleted" => 0,
                "create_time" => $nowTime,
                "update_time" => $nowTime,
            ];
            // 插入新的本日数据
            $affectRows = app("model_data_summary_daily")->create($insertParams);
            if (!$affectRows) {
                throw new BasicException(10001, "插入新数据失败！");
            }
        }, 5);

        return true;
    }

    // 批量获取某日用户收到礼物价值
    public function batchGetUserReceiveGiftDaily(Carbon $date) {
        $startTime = $date->startOfDay()->timestamp;
        $endTime = $date->endOfDay()->timestamp;

        return app("model_give_gift")->where([
            ["add_time", ">=", $startTime],
            ["add_time", "<=", $endTime],
        ])->select([
            DB::raw("gift_user as user_id"),
            DB::raw("sum( total_price ) AS receive_gift_value ")
        ])->groupBy("gift_user")->get();
    }

    public function insertUserDailyWithDate($params, Carbon $date)
    {
        DB::connection("mysql")->transaction(function () use ($params, $date) {
            $nowTime = time();
            // 将存在的本日数据删除
            app("model_data_summary_daily_user")
                ->where([
                    ["date", "=", $date->format("Y-m-d")]
                ])
                ->delete();

            $insertParams = [];
            foreach ($params as $item) {
                $insertParams[] = [
                    "date" => $date->format("Y-m-d"),
                    "user_id" => $item['user_id'],
                    "receive_gift_value" => $item['receive_gift_value'],
                    "is_deleted" => 0,
                    "create_time" => $nowTime,
                    "update_time" => $nowTime,
                ];
            }

            // 插入新的本日数据
            $affectRows = app("model_data_summary_daily_user")->insert($insertParams);
            if (!$affectRows) {
                throw new BasicException(10001, "插入新数据失败！");
            }
        }, 5);

        return true;
    }

    // 批量获取用户汇总数据
    public function batchGetUserTotal() {

        return app("model_data_summary_daily_user")->select([
            "user_id",
            DB::raw("sum( receive_gift_value ) AS receive_gift_value ")
        ])->groupBy("user_id")->get();
    }

    // 批量插入用户汇总数据
    public function insertUserTotal($params, $isClear = false)
    {
        DB::connection("mysql")->transaction(function () use ($params, $isClear) {
            if ($isClear) {
                $affectRows = app("model_data_summary_user")->truncate();
                if (!$affectRows) {
                    throw new BasicException(10001, "清空数据失败！");
                }
            }

            $nowTime = time();

            $insertParams = [];
            foreach ($params as $item) {
                $insertParams[] = [
                    "user_id" => $item['user_id'],
                    "receive_gift_value" => $item['receive_gift_value'],
                    "is_deleted" => 0,
                    "create_time" => $nowTime,
                    "update_time" => $nowTime,
                ];
            }

            // 插入新的数据
            $affectRows = app("model_data_summary_user")->insertOrUpdate($insertParams, [
                "receive_gift_value" => DB::raw("receive_gift_value + values(`receive_gift_value`)"),
                "update_time" => DB::raw("values(`update_time`)")
            ]);
            if (!$affectRows) {
                throw new BasicException(10001, "插入新数据失败！");
            }
        }, 5);

        return true;
    }

}